package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Region;
import interfaces.IRegion;

public class CRegion implements IRegion {
  private CConnection db;

  public CRegion(CConnection db) {
	this.db = db;
  }
	
  @Override
  public boolean create(Region region) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("insert into region (regionid, description, stateid) values ("+region.getRegionID()+", '"+region.getDescription()+"',  "+region.getStateID()+")");
    return i > 0;
  }

  @Override
  public boolean update(Region region) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("UPDATE region SET description='"+region.getDescription()+"', stateid='"+region.getStateID()+"' WHERE regionid="+region.getRegionID());
    return i > 0;
  }
  
  @Override
  public boolean delete(Region region) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("delete from region where regionid='"+region.getRegionID()+"'");
    return i > 0;
  }

  @Override
  public ArrayList<Region> retrieveByID(int regid) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from region where regionid = " + regid);

    ArrayList<Region> regions = new ArrayList<Region>();
    
    while(res.next()) {
      Region r = new Region();
      r.setRegionID(res.getInt("RegionID"));
      r.setDescription(res.getString("Description"));
      r.setStateID(res.getInt("StateID"));
      
      regions.add(r);
    }

    return regions;
  }

  @Override
  public ArrayList<Region> retrieveAll() throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from region");
		
    ArrayList<Region> regions = new ArrayList<Region>();
    
    while(res.next()) {
    	Region r = new Region();
        r.setRegionID(res.getInt("RegionID"));
        r.setDescription(res.getString("Description"));
        r.setStateID(res.getInt("StateID"));
        
        regions.add(r);
    }
    
    return regions;
  }
}